import pandas as pd
from datetime import datetime
from pandas import ExcelWriter

ywj = r'文档/档约比对.xlsx'
xins = input('请输入签约下载文件名（含扩展名）: ')
parts = xins.split('.')
names = parts[0]
cr = f'文档/{xins}'

# 初始化四个结果集
original_list = []  # 原始数据
disease_based = []  # 疾病史基准
resident_based = []  # 居民情况基准
service_based = []  # 服务包基准


def create_record_template(obj, base_field):
    """创建标准记录模板"""
    return {
        '身份证号': obj['身份证号'],
        '年龄': obj['年龄'],
        '用户姓名': obj['用户姓名'],
        '基准内容': obj[base_field],
        '居民情况缺失': [],
        '服务包缺失': [],
        '疾病史缺失': []
    }


# 读取数据
lon = pd.read_excel(ywj, dtype=object).fillna('')
news = pd.read_excel(cr, dtype=object).fillna('')
lon['身份证号'] = lon['身份证号'].str.strip()
news['身份证号'] = news['身份证号'].str.strip()

for i, news_row in news.iterrows():
    xb = news_row['身份证号']
    match_index = lon.index[lon['身份证号'] == xb].tolist()

    if not match_index:
        continue

    wz = match_index[0]
    lon_row = lon.iloc[wz]# 获取 lon 行

    # 基础信息处理
    current_year = datetime.now().year
    birth_year = int(str(xb)[6:10]) if len(str(xb)) >= 14 else None
    age = current_year - birth_year if birth_year else None

    # 合并疾病史
    diseases = []
    for col in ['疾病一', '疾病二', '疾病三', '疾病四', '疾病五']:
        disease = str(lon_row[col]).strip()
        if disease and disease != 'nan':
            diseases.append(disease)
    disease_history = ','.join(diseases) if diseases else ''

    # 原始数据记录
    original_record = {
        '身份证号': xb,
        '年龄': age,
        '用户姓名': news_row['用户姓名'],
        '基本居民情况': str(news_row['基本居民情况']).strip(),
        '服务包': str(news_row['服务包']).strip(),
        '疾病史': disease_history,
        '居民情况缺失': [],
        '服务包缺失': [],
        '疾病史缺失': []
    }

    # 创建三个基准记录
    disease_record = create_record_template(original_record, '疾病史')
    resident_record = create_record_template(original_record, '基本居民情况')
    service_record = create_record_template(original_record, '服务包')

    # ========== 疾病史基准检查 ==========
    if disease_history:
        # 高血压相关
        if '高血压' in disease_history:
            if '高血压患者' not in original_record['基本居民情况']:
                disease_record['居民情况缺失'].append('高血压患者')
            if '高血压' not in original_record['服务包']:
                disease_record['服务包缺失'].append('高血压服务包')

        # 糖尿病相关
        if '糖尿病' in disease_history:
            if '糖尿病患者' not in original_record['基本居民情况']:
                disease_record['居民情况缺失'].append('糖尿病患者')
            if '糖尿病' not in original_record['服务包']:
                disease_record['服务包缺失'].append('糖尿病服务包')

        # 冠心病相关
        if '冠心病' in disease_history:
            if '冠心病患者' not in original_record['基本居民情况']:
                disease_record['居民情况缺失'].append('冠心病患者')
            if '冠心病' not in original_record['服务包']:
                disease_record['服务包缺失'].append('冠心病服务包')

        # 慢性阻塞性肺疾病相关
        if '慢性阻塞性肺疾病' in disease_history:
            if '慢阻肺患者' not in original_record['基本居民情况']:
                disease_record['居民情况缺失'].append('慢阻肺患者')
            if '慢性阻塞性肺疾病' not in original_record['服务包']:
                disease_record['服务包缺失'].append('慢阻肺患者家庭医生签约服务包')

    # ========== 居民情况基准检查 ==========
    resident_status = original_record['基本居民情况']
    if resident_status:
        # 高血压患者
        if '高血压患者' in resident_status:
            if '高血压' not in disease_history:
                resident_record['疾病史缺失'].append('高血压')
            if '高血压' not in original_record['服务包']:
                resident_record['服务包缺失'].append('高血压服务包')

        # 慢阻肺患者
        if '慢阻肺患者' in resident_status:
            if '慢性阻塞性肺疾病' not in disease_history:
                resident_record['疾病史缺失'].append('慢性阻塞性肺疾病')
            if '慢性阻塞性肺疾病' not in original_record['服务包']:
                resident_record['服务包缺失'].append('慢阻肺患者家庭医生签约服务包')

        # 65岁以上老人
        if age and age >= 65:
            if '老年人' not in resident_status:
                original_record['居民情况缺失'].append('65岁及以上老年人')
            if '老年人' not in original_record['服务包']:
                resident_record['服务包缺失'].append('老年人服务包')
            # if '老年人' not in disease_history:  # 如果疾病史需要记录老年病
            #     resident_record['疾病史缺失'].append('老年相关疾病')

    # ========== 服务包基准检查 ==========
    service_package = original_record['服务包']
    if service_package:
        # 高血压服务包
        if '高血压' in service_package:
            if '高血压' not in disease_history:
                service_record['疾病史缺失'].append('高血压')
            if '高血压患者' not in resident_status:
                service_record['居民情况缺失'].append('高血压患者')

        # 慢阻肺服务包
        if '慢性阻塞性肺疾病' in service_package:
            if '慢性阻塞性肺疾病' not in disease_history:
                service_record['疾病史缺失'].append('慢性阻塞性肺疾病')
            if '慢阻肺患者' not in resident_status:
                service_record['居民情况缺失'].append('慢阻肺患者')

        # 儿童服务包
        if '儿童' in service_package:
            if age and age > 6:
                service_record['居民情况缺失'].append('年龄超过6岁')
            if '0-6岁儿童' not in resident_status:
                service_record['居民情况缺失'].append('0-6岁儿童')

    # 收集记录
    original_list.append(original_record)
    disease_based.append(disease_record)
    resident_based.append(resident_record)
    service_based.append(service_record)

# 转换为DataFrame
df_original = pd.DataFrame(original_list)
df_disease = pd.DataFrame(disease_based)
df_resident = pd.DataFrame(resident_based)
df_service = pd.DataFrame(service_based)

# 写入Excel
output_path = f'执行结果/{names}_多维核查.xlsx'
with ExcelWriter(output_path) as writer:
    df_disease.to_excel(writer, sheet_name='疾病史基准', index=False)
    df_resident.to_excel(writer, sheet_name='居民情况基准', index=False)
    df_service.to_excel(writer, sheet_name='服务包基准', index=False)
    df_original.to_excel(writer, sheet_name='原始数据', index=False)

print(f'文件已生成：{output_path}')
print(f'包含四个工作表：\n1. 疾病史基准\n2. 居民情况基准\n3. 服务包基准\n4. 原始数据')
